import os
import sys
import pandas as pd
from datetime import datetime
import re
from openpyxl.styles import Font
from openpyxl import load_workbook
table = {ord(f):ord(t) for f,t in zip(
     u'，。！？【】（）％＃＠＆１２３４５６７８９０',
     u',.!?[]()%#@&1234567890')}

workbook = load_workbook(filename="data_score_glm.xlsx")
sheet = workbook.active
cell = sheet["A3"]
font = Font(bold=True,color="00FF0000")
len_sheet = sheet.max_row-1
v1_sum=0;v2_sum=0;v3_sum=0;v4_sum=0;v_sum=0
print(len_sheet)
for i in range(sheet.max_row-1):
    v1_flag=0;v2_flag=0;v3_flag=0;v4_flag=0
    print(i+2, '::',sheet['B'+str(i+2)].value)

    celldf_name=str(sheet['C'+str(i+2)].value).translate(table).replace(" ","") #### 商户
    celljson_name=str(sheet['J'+str(i+2)].value).translate(table).replace(" ","")
    if celldf_name in celljson_name or celljson_name in celldf_name:
        v1_sum+=1; v1_flag=1
    else:
        sheet['J'+str(i+2)].font = font

    celldf_number=str(sheet['E'+str(i+2)].value) #### 订单
    celljson_number=str(sheet['K'+str(i+2)].value)
    if celldf_number.translate(table)==celljson_number.translate(table):
        v2_sum+=1; v2_flag=1
    else:
        sheet['K'+str(i+2)].font = font

    celldf_time=str(sheet['I'+str(i+2)].value) #### 日期
    celljson_time=str(sheet['L'+str(i+2)].value)
    try:
        time_df=pd.to_datetime(celldf_time)
        time_json=pd.to_datetime(celljson_time)
        time_json_str=str(time_json.year)+"-"+str(time_json.month)+"-"+str(time_json.day)
        time_df_str=str(time_df.year)+"-"+str(time_df.month)+"-"+str(time_df.day)
        print(":::",time_json_str, time_df_str)
        if time_json_str == time_df_str:
            v3_sum+=1; v3_flag=1
        else:
            sheet['L'+str(i+2)].font = font
    except Exception as e:
        print('日期 错误了:',e)
        sheet['L'+str(i+2)].font = Font(bold=True,color="000000FF")

    celldf_amount=str(sheet['G'+str(i+2)].value) #### 金额
    celljson_amount=str(sheet['M'+str(i+2)].value)
    try:
        amount_df=re.findall("\d+\.?\d*", celldf_amount)
        amount_json=re.findall("\d+\.?\d*", celljson_amount)
        if float(''.join(amount_json)) == float(''.join(amount_df)):
            v4_sum+=1; v4_flag=1
        else:            
            sheet['M'+str(i+2)].font = font
    except Exception as e:
        print('金额 错误了:',e)
    
    if v1_flag==1 and v2_flag==1 and v3_flag==1 and v4_flag==1:
        v_sum+=1

print("商户名称_score:",v1_sum/len_sheet)
print("订单编号_score:",v2_sum/len_sheet)
print("交易日期_score:",v3_sum/len_sheet)
print("交易金额_score:",v4_sum/len_sheet)
print("v_score:",v_sum/len_sheet)
workbook.save(filename="data_score_result_glm.xlsx")

"""

import xlrd,xlwt
from xlwt import *
sheet_data = xlrd.open_workbook('data_csv_.xls')
print(sheet_data)


table = sheet_data.sheet_by_name('Sheet1')
print(table)

rows = table.nrows


style2 = XFStyle()
# 设置背景色为红色
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x0A
style2.pattern = pattern


sheet_data.write(3, 1, "", style2)





for row in range(rows):
    data = table.cell(row, 1).value
    print('row_data',data)



import openpyxl
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment

# 1.打开工作薄
wb = openpyxl.load_workbook("data_csv_.xlsx")
sheet1 = wb["Sheet1"]


print("a1:",sheet1.max_row)

# 创建一个加粗的格式对象
bold_format = workbook.add_format({'bold': True})

# 在单元格中写入数据，并应用加粗格式
sheet1.write(3, 1, bold_format)



for i in range(sheet1.max_row-1):
    print(i+2)
    df_name=sheet1.cell(row=i+2,column=4).value
    json_name=sheet1.cell(row=i+2,column=11).value
    print(df_name,json_name)

"""




"""





from datetime import datetime

string_date = "2022-1-01 10:40:08"
format = "%Y/%m-%d"
datetime_object = datetime.strptime(string_date, "%Y-%m-%d")

print(datetime_object)


print(len(df1)) 
v1_sum=0;v2_sum=0;v3_sum=0;v4_sum=0;v_sum=0
v1_flag=0;v2_flag=0;v3_flag=0;v4_flag=0
for i in df1.index.values:#获取行号的索引，并对其遍历
    print("",i) 
    dir_name=df1.loc[i,'店铺名称（与文件夹一致）']
    img_name=df1.loc[i,'img']
    img_path=os.path.join('data',dir_name,img_name)+'.jpg'
    #print("img_path::",img_path)
    image = Image.open(img_path)
    #print('mode:',image.mode)
    dateString=df1.loc[i,'（消费时间）']
    print(dateString,type(dateString))
    df1['（消费时间）'] = pd.to_datetime(df1['（消费时间）'])
    dateString=df1.loc[i,'（消费时间）']
    print(dateString,type(dateString))    
    #print(datetime.strptime(str(dateString), "%d/%m/%Y"))
"""










